package org.smile.db.sql;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.smile.db.DbManager;
import org.smile.db.Dialect;
import org.smile.db.PageModel;
import org.smile.db.handler.MapRowHandler;
import org.smile.db.handler.RowHandler;
import org.smile.db.sql.page.DialectPage;
import org.smile.db.sql.page.MySQLDialectPage;
import org.smile.db.sql.page.OracleDialectPage;
import org.smile.db.sql.page.RowNumberDialectPage;
import org.smile.db.sql.page.SQL2000DialectPage;
import org.smile.db.sql.page.SQLServerDialectPage;
import org.smile.json.JSONObject;
import org.smile.util.RegExp;
/**
 * 数据库操作者
 * @author 胡真山
 *
 */
public class SQLRunner {
	
	protected final static Logger logger = Logger.getLogger(SQLRunner.class);
	/**
	 * 数据库言，默认为oracle
	 */
	private int dbDialect=Dialect.ORACLE;
	/**
	 * 连接是否自动提交
	 */
	private boolean isAutoCommit=true;
	/**
	 * 是否支持处理 类型检查
	 */
	private volatile boolean pmdKnownBroken = true;
	/**
	 * 数据库连接
	 */
	private Connection conn;
	/**
	 * 结果集处理类
	 */
	private RowHandler handler;
	/**
	 * 构造方法
	 * @param conn
	 */
	public SQLRunner(Connection conn){
		this.conn=conn;
		handler=new MapRowHandler();
	}
	/**
	 * 构造方法
	 * @param conn
	 */
	public SQLRunner(Connection conn,RowHandler handler){
		this.conn=conn;
		this.handler=handler;
	}
	/**
	 * 构造方法 会连接一个默认的数据库
	 * @param conn
	 */
	public SQLRunner(){
		handler=new MapRowHandler();
	}
	/**
	 * 
	 * 执行一条sql语句 
	 * @param sql 
	 * @param params 要设置的参数 的一个数组
	 * @return 
	 * @throws Exception 
	 */
	public int executeUpdate(String sql,Object... params) throws SQLException
	{
		PreparedStatement ps=null;
	    try{
		    ps=conn.prepareStatement(sql);
		    this.fillStatement(ps, params);
		    return ps.executeUpdate();
	    }finally{
	    	if(ps!=null){
	    		ps.close();
	    	}
	    }
	}
	/**
	 * 查询一条sql语句
	 *   list中的对象类型由handler对象决定 
	 *   如果不指定handler 将使用默认的MapRowHandler返回的是一个Map的List
	 * 查询一条语句
	 * @param sql
	 * @param params
	 * @return 
	 * @throws SQLException 
	 * @throws Exception
	 */
	public List query(String sql,Object... params) throws SQLException 
	{
	    PreparedStatement ps=null;
	    try{
		    ps=conn.prepareStatement(sql);
		    this.fillStatement(ps, params);
		    ResultSet rs=ps.executeQuery();
		    return this.parseRsToList(rs);
	    }finally{
	    	if(ps!=null){
	    		ps.close();
	    	}
	    }
		
	}
	
	/**
	 * 查询一条语句
	 * @param sql
	 * @param params
	 * @return 
	 * @throws SQLException 
	 * @throws Exception
	 */
	public List query(String sql,Map params) throws SQLException {
		ParamParser sqlParams=new ParamParser(sql,params);
		return this.query(sqlParams.sql, sqlParams.params);
	}
	/**
	 * 打开默认的连接
	 */
	public void openConnection() throws Exception
	{
		if(this.conn==null||this.conn.isClosed())
		{
			this.conn=DbManager.getConnection();
		}
	}
	/**
	 * 不自动提交
	 * 开启手动提交
	 */
	public void beginTransaction() throws SQLException
	{
		this.isAutoCommit=conn.getAutoCommit();
		this.conn.setAutoCommit(false);
	}
	/**
	 * 提交
	 */
	public void commit() throws SQLException
	{
		try{
			this.conn.commit();
		}finally{
			this.conn.setAutoCommit(this.isAutoCommit);
		}
	}
	/**
	 * 执行一条sql语句
	 * @param sql 
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public boolean execute(String sql,Object... params) throws SQLException
	{
	   PreparedStatement ps=null;
	   try{
		   ps=conn.prepareStatement(sql);
		   this.fillStatement(ps, params);
		   return ps.execute();
	   }finally{
		   if(ps!=null){
			   ps.close();
		   }
	   }
	}
	/**
	 * 
	 * @param sql SELECT * FROM T WHERE name=:name
	 * @param params {name:胡真山}
	 * @return 
	 * @throws SQLException
	 */
	public boolean execute(String sql,Map params) throws SQLException{
		ParamParser sqlParams=new ParamParser(sql,params);
		return this.execute(sqlParams.sql, sqlParams.params);
	}
	/**
	 * 回滚
	 */
	public void rollback() throws Exception
	{
		this.conn.rollback();
	}
	/**
	 * 反result 转成 map
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public List parseRsToList(ResultSet rs) throws SQLException
	{
		List dataList = new ArrayList();
		try {
	        while (rs.next()) {
	            dataList.add(this.handler.handle(rs));
	        }
		} catch (SQLException e) {
			throw new SQLException("把结果集rs转成List出现错误:"+e);
		}finally{
            if(rs != null && rs.getStatement()!= null){
                rs.getStatement().close();
            }
		}
        return dataList;
	}
	/**
	 * 支持top 分页的数据库分布查询 
	 * 如果是sql2005以版本 推荐使用 querySql2005PageSQL 方法
	 * @param sql
	 * @param page
	 * @param size
	 * @return
	 * @throws SQLException 
	 */
	@SuppressWarnings("unchecked")
	public PageModel queryTopPageSql(String sql,Object[] params,int page,int size) throws SQLException
	{
		PageModel model=new PageModel();
		model.setPage(page);
		model.setSize(size);

		int firstRow=(page-1)*size;
		int endRow=firstRow+size;
		
		RegExp reg=new RegExp(" order +by ",false); 
		
		//最后匹配的结束
		int indexStart=reg.lastIndex(sql);
		//处理后的sql
		String newSql=sql;
		if(indexStart>0){
			newSql=sql.substring(0,indexStart);
		}
		String countSql="SELECT COUNT(*) FROM ("+newSql+") COUNT_TABLE_T";
		
		StringBuffer dataSql=new StringBuffer("SELECT TOP "+endRow+" * FROM ("+newSql+") DATA_TABLE_T");
		if(indexStart>0){
			dataSql.append(sql.substring(indexStart,sql.length()));
		}
		PreparedStatement ps=null;
		try{
			ps=conn.prepareStatement(countSql);
			this.fillStatement(ps, params);
			ResultSet countRs=ps.executeQuery();
			if(countRs.next())
			{
				long totals=(long)countRs.getLong(1);
				//总条数
				model.setTotal(totals);
				//总页数
				model.setTotalPages(totals%size==0?totals/size:totals/size+1);
			}
			//关闭记数statement
			ps.close();
			logger.debug(dataSql.toString());
			//数据sql查询
			ps=conn.prepareStatement(dataSql.toString());
			this.fillStatement(ps, params);
			ResultSet dataRs=ps.executeQuery();
			//跳过每条记录前面所有的记录
			for(int i=0;i<firstRow&&dataRs.next();i++);
			//处理第一条记录以后的数据
			List dataList = new ArrayList();
	        while (dataRs.next()) {
	            dataList.add(this.handler.handle(dataRs));
	        }
	        model.setRows(dataList);
	        //
			return model;
		}finally{
			if(ps!=null){
				ps.close();
			}
		}
	}
	/**
	 * 适用于只能用top的数据库
	 * @param sql
	 * @param params
	 * @param page
	 * @param size
	 * @return
	 * @throws SQLException
	 */
	public PageModel queryTopPageSql(String sql,Map params,int page,int size) throws SQLException{
		ParamParser sqlParams=new ParamParser(sql,params);
		return this.queryTopPageSql(sqlParams.sql, sqlParams.params, page, size);
	}
	/**
	 * 批量执行sql
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	 public int[] batch(String sql, Object[][] params) throws SQLException {
        int[] rows = null;
        PreparedStatement stmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                this.fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            rows = stmt.executeBatch();
        return rows;
	 }
	 /**
	  * 设置一行数据值
	  * @param stmt 
	  * @param params 参数
	  * @throws SQLException
	  */
	 public void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {
		 	if (params == null) {
	            return;
	        }
	        ParameterMetaData pmd = null;
	        //检查参数是不是一样多
	        if (pmdKnownBroken) {
	        	int stmtCount =0;
	        	int paramsCount =0;
	        	try{
		            pmd = stmt.getParameterMetaData();
		            stmtCount= pmd.getParameterCount();
		            paramsCount= params == null ? 0 : params.length;
	        	}catch(Throwable e){
	        		//如果出现错误，证明PreparedStatement的该实现不支持参数检查
	        		//下次操作将不检查了
	        		logger.error("数据库不支持数据类型检查方法getParameterMetaData",e);
	        		pmdKnownBroken=false;
	        	}
	        	if (stmtCount != paramsCount) {
	                throw new SQLException("参数数据组度与sql中的参数个数不一样[实际"
	                        + stmtCount + ", 给定的： " + paramsCount+"]");
	            }
	        }
	        //循环设置值
	        for (int i = 0; i < params.length; i++) {
	            if (params[i] != null) {
	            	Object valueStr=params[i];
	            	if(params[i] instanceof Collection||params[i] instanceof Map){
	            		valueStr=JSONObject.toJSONString(params[i]);
	            	}
	                stmt.setObject(i + 1, valueStr);
	            } else {
	                int sqlType = Types.VARCHAR;
	                if (pmdKnownBroken) {
	                    try {
	                    	//如果支持类型参数检查去表中查看类型
	                        sqlType = pmd.getParameterType(i + 1);
	                    } catch (SQLException e) {
	                        pmdKnownBroken = false;
	                    }
	                }
	                stmt.setNull(i + 1, sqlType);
	            }
	        }
	}
	/**
	 * 设置连接
	 * @param conn
	 */
	public void setConn(Connection conn) {
		this.conn = conn;
	}
	/**
	 * 设置结果集处理Handler
	 */
	public void setHandler(RowHandler handler) {
		this.handler = handler;
	}
	/**
	 * 执行一条sql语句
	 */
	public int executeUpdate(String sql,Map params) throws Exception {
		ParamParser sqlParams=new ParamParser(sql,params);
		return this.executeUpdate(sqlParams.sql,sqlParams.params);
	}
	
	/**
	 * 查询结果为单对象 适用于只有结果只有一行数据的 对象的类型由RowHanndler决定
	 * 可能是一个bean 、list ,array ,map
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public Object queryForObject(String sql) throws SQLException{
		Object[] params=null;
		return this.queryForObject(sql, params);
	}
	/**
	 * 查询结果为单对象 适用于只有结果只有一行数据的 对象的类型由RowHanndler决定
	 * 可能是一个bean 、list ,array ,map
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public Object queryForObject(String sql,Object... params) throws SQLException{
		List list=this.query(sql,params);
		if(list!=null&&list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	/**
	 * 查询结果为单对象 适用于只有结果只有一行数据的 对象的类型由RowHanndler决定
	 * 可能是一个bean 、list ,array ,map
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public Object queryForObject(String sql,Map params) throws SQLException{
		
		List list=this.query(sql,params);
		if(list!=null&&list.size()>0){
			return list.get(0);
		}else{
			return null;
		}
	}
	/**
	 * 分页查询数据
	 * @param countSql 总条数语句
	 * @param dataSql 数据语句
	 * @param page
	 * @param size
	 * @return
	 */
	public PageModel queryPageSql(String countSql,String dataSql,int page,int size,Object... params) throws SQLException{
		logger.debug(dataSql.toString());
		//分页数据模型
		PageModel model=new PageModel();
		model.setPage(page);
		model.setSize(size);
		PreparedStatement ps=null;
		try{
			ps=conn.prepareStatement(countSql);
			this.fillStatement(ps, params);
			ResultSet countRs=ps.executeQuery();
			if(countRs.next())
			{
				long totals=(long)countRs.getLong(1);
				//总条数
				model.setTotal(totals);
				//总页数
				model.setTotalPages(totals%size==0?totals/size:totals/size+1);
			}
			//关闭记数statement
			ps.close();
			ps=conn.prepareStatement(dataSql);
			this.fillStatement(ps, params);
			ResultSet dataRs=ps.executeQuery();
			//处理第一条记录以后的数据
			List dataList = new ArrayList();
	        while (dataRs.next()) {
	            dataList.add(this.handler.handle(dataRs));
	        }
	        model.setRows(dataList);
	        //
			return model;
		}finally{
			if(ps!=null){
				ps.close();
			}
		}
	}
	/**
	 * 查询前N条数据
	 * @param sql 数据语句
	 * @param size
	 * @return
	 */
	public List queryTop(String sql,int size,Object... params) throws SQLException{
		String dataSql=sql;
		switch(this.dbDialect){
			case Dialect.ORACLE:dataSql="select * from ("+sql+") where rownum <="+size;break;
			case Dialect.SQLSERVER2000:
			case Dialect.SQLSERVER2005:
			case Dialect.HSQL:dataSql="SELECT TOP "+size+" * FROM ("+sql+") DATA_TABLE_T";break;
			case Dialect.APACHE_DERBY:dataSql=new RowNumberDialectPage(sql).getDataSql(1, size);break;
			case Dialect.DB2:dataSql=new RowNumberDialectPage(sql).getDataSql(1, size);break;
			case Dialect.MYSQL:dataSql=new MySQLDialectPage(sql).getDataSql(1, size);break;
			default:throw new SQLException("未知的数据库方言，请实现[DialectPage]接口自定义分页");
		}
		//分页数据模型
		PreparedStatement ps=null;
		try{
			ps=conn.prepareStatement(dataSql);
			this.fillStatement(ps, params);
			ResultSet dataRs=ps.executeQuery();
			//处理第一条记录以后的数据
			List dataList = new ArrayList();
	        while (dataRs.next()) {
	            dataList.add(this.handler.handle(dataRs));
	        }
			return dataList;
		}catch(Exception e){
			throw new SQLException("sql==>"+dataSql+e);
		}finally{
			if(ps!=null){
				ps.close();
			}
		}
	}
	/**
	 * 分页查询数据
	 * @param countSql
	 * @param dataSql
	 * @param page
	 * @param size
	 * @return
	 */
	public PageModel queryPageSql(String sql,int page,int size,Object... params) throws SQLException{
		switch(this.dbDialect){
			case Dialect.ORACLE:return this.queryPageSql(new OracleDialectPage(sql), page, size, params);
			case Dialect.SQLSERVER2000:return this.queryPageSql(new SQL2000DialectPage(sql), page, size, params);
			case Dialect.SQLSERVER2005:return this.queryPageSql(new SQLServerDialectPage(sql), page, size, params);
			case Dialect.HSQL:return this.queryTopPageSql(sql, params, page, size);
			case Dialect.APACHE_DERBY:return this.queryPageSql(new RowNumberDialectPage(sql), page, size, params);
			case Dialect.DB2:return this.queryPageSql(new RowNumberDialectPage(sql), page, size, params);
			case Dialect.MYSQL:return this.queryPageSql(new MySQLDialectPage(sql), page, size, params);
			default:throw new SQLException("未知的数据库方言，请实现[DialectPage]接口自定义分页");
		}
	}
	/**
	 * 分页查询
	 * @param sql select * from t where name=:name
	 * @param params {name:test}
	 * @param page
	 * @param size
	 * @return
	 * @throws SQLException
	 */
	public PageModel queryPageSql(String sql,Map params,int page,int size) throws SQLException{
		ParamParser sqlParams=new ParamParser(sql,params);
		return this.queryPageSql(sqlParams.sql, page, size,sqlParams.params);
	}
	/**
	 * 分页查询数据
	 * @param dialectPage 提供 getCountSql 与getDataSql两个方法 的接口
	 * @param params
	 * @param page
	 * @param size
	 * @return
	 */
	public PageModel queryPageSql(DialectPage dialectPage,int page,int size,Object... params) throws SQLException{
		return this.queryPageSql(dialectPage.getCountSql(), dialectPage.getDataSql(page, size), page, size, params);
	}
	/**
	 * 关闭数据库连接 
	 */
	public void closeConn(){
		if(this.conn!=null){
			try {
				this.conn.setAutoCommit(this.isAutoCommit);
				this.conn.close();
			} catch (SQLException e) {
				logger.error("关闭数据库连接出现错误",e);
			}
		}
	}
	/**
	 * 设置数据库方言
	 * @param dbDialect
	 */
	public void setDbDialect(int dbDialect) {
		this.dbDialect = dbDialect;
	}
	public void setPmdKnownBroken(boolean pmdKnownBroken) {
		this.pmdKnownBroken = pmdKnownBroken;
	}
	
}
